﻿Imports System.Net
Imports System.IO
Imports System.Text
Imports Newtonsoft.Json
Imports Newtonsoft.Json.Linq
Public Class PayrollManager
    Dim sqlManager As New SQLManager
    Public Function getAllSSS() As List(Of SSS)
        Dim jsonResponse As String = sqlManager.executeQuery("SELECT * FROM tbl_sss")
        getAllSSS = JsonConvert.DeserializeObject(Of List(Of SSS))(jsonResponse)
    End Function
    Public Function getItemSSS(ByVal id As String) As List(Of SSS)
        Dim jsonResponse As String = sqlManager.executeQuery("SELECT * FROM tbl_sss WHERE ID = " + id)
        getItemSSS = JsonConvert.DeserializeObject(Of List(Of SSS))(jsonResponse)
    End Function
    Public Function insertItemSSS(ByVal bracket As String, ByVal minimumSalary As String, ByVal maximumSalary As String, ByVal monthlySalary As String, ByVal employerShare As String, ByVal employeeShare As String, ByVal employeeCompensation As String) As List(Of SSS)
        sqlManager.executeUpdate("INSERT INTO tbl_sss (bracket,min_salary,max_salary,monthly_salary,employer_share,employee_share, employee_compensation) VALUE (" + bracket + "," + minimumSalary + "," + maximumSalary + "," + monthlySalary + "," + employerShare + "," + employeeShare + "," + employeeCompensation + ")")
    End Function
    Public Function updateItemSSS(ByVal bracket As String, ByVal minimumSalary As String, ByVal maximumSalary As String, ByVal monthlySalary As String, ByVal employerShare As String, ByVal employeeShare As String, ByVal employeeCompensation As String, ByVal id As String) As List(Of SSS)
        sqlManager.executeUpdate("UPDATE tbl_sss SET bracket=" + bracket + " , min_salary=" + minimumSalary + ", max_salary=" + maximumSalary + ", monthly_salary=" + employerShare + ", employer_share=" + employerShare + ", employee_share=" + employeeShare + ", employee_compensation=" + employeeCompensation + " WHERE id=" + id + " ")
    End Function
    Public Function deleteItemSSS(ByVal id As String) As List(Of SSS)
        sqlManager.executeUpdate("DELETE FROM tbl_sss WHERE id = " + id)
    End Function
    Public Function getAllPagIBIG() As List(Of PagIBIG)
        Dim jsonResponse As String = sqlManager.executeQuery("SELECT * FROM tbl_pagibig")
        getAllPagIBIG = JsonConvert.DeserializeObject(Of List(Of PagIBIG))(jsonResponse)
    End Function
    Public Function getItemPagIBIG(ByVal id As String) As List(Of PagIBIG)
        Dim jsonResponse As String = sqlManager.executeQuery("SELECT * FROM tbl_pagibig WHERE ID = " + id)
        getItemPagIBIG = JsonConvert.DeserializeObject(Of List(Of PagIBIG))(jsonResponse)
    End Function
    Public Function insertItemPagIBIG(ByVal minimumSalary As String, ByVal maximumSalary As String, ByVal employerShare As String, ByVal employeeShare As String, ByVal maximumShare As String) As List(Of PagIBIG)
        sqlManager.executeUpdate("INSERT INTO tbl_pagibig (min_salary, max_salary, employer_share, employee_share, min_share) VALUE (" + minimumSalary + "," + maximumSalary + "," + employerShare + "," + employeeShare + "," + maximumShare + ")")
    End Function
    Public Function updateItemPagIBIG(ByVal minimumSalary As String, ByVal maximumSalary As String, ByVal employerShare As String, ByVal employeeShare As String, ByVal maximumShare As String, ByVal id As String) As List(Of PagIBIG)
        sqlManager.executeQuery("UPDATE tbl_pagibig SET min_salary=" + minimumSalary + ", max_salary=" + maximumSalary + ", employer_share=" + employerShare + ", employee_share=" + employeeShare + ", min_share=" + maximumShare + " WHERE id=" + id + "")
    End Function
    Public Function deleteItemPagIBIG(ByVal id As String) As List(Of PagIBIG)
        sqlManager.executeUpdate("DELETE FROM tbl_pagibig WHERE id = " + id)
    End Function
    Public Function getAllPHI() As List(Of PhilHealth)
        Dim jsonResponse As String = sqlManager.executeQuery("SELECT * FROM tbl_philhealth")
        getAllPHI = JsonConvert.DeserializeObject(Of List(Of philhealth))(jsonResponse)
    End Function
    Public Function getItemPHI(ByVal id As String) As List(Of PhilHealth)
        Dim jsonResponse As String = sqlManager.executeQuery("SELECT * FROM tbl_philhealth WHERE ID = " + id)
        getItemPHI = JsonConvert.DeserializeObject(Of List(Of PhilHealth))(jsonResponse)
    End Function
    Public Function insertItemPHI(ByVal philObject As PhilHealth) As List(Of PhilHealth)
        Dim queryString As String = "INSERT INTO tbl_philhealth (bracket,min_salary, max_salary, base_salary, employer_share, employee_share) VALUE (" + philObject.bracket.ToString + "," + philObject.min_salary.ToString + "," + philObject.max_salary.ToString + "," + philObject.base_salary.ToString + "," + philObject.employer_share.ToString + "," + philObject.employee_share.ToString + ")"
        sqlManager.executeUpdate(queryString)
    End Function
    Public Function updateItemPHI(ByVal braket As String, ByVal minimumSalary As String, ByVal maximumSalary As String, ByVal salaryBase As String, ByVal employerShare As String, ByVal employeeShare As String, ByVal id As String) As List(Of PhilHealth)
        sqlManager.executeQuery("UPDATE tbl_philhealth SET bracket=" + braket + ",min_salary = " + minimumSalary + ", max_salary = " + maximumSalary + ", base_salary =" + salaryBase + " , employer_share =" + employerShare + " , employee_share = " + employeeShare + " WHERE id = " + id + "")
    End Function
    Public Function deleteItemPHI(ByVal id As String) As List(Of PhilHealth)
        sqlManager.executeUpdate("DELETE FROM tbl_philhealth WHERE id = " + id)
    End Function
    Public Function getAllLeaveType() As List(Of LeaveType)
        Dim jsonResponse As String = sqlManager.executeQuery("SELECT * FROM ref_leave_type")
        getAllLeaveType = JsonConvert.DeserializeObject(Of List(Of LeaveType))(jsonResponse)
    End Function
    Public Function getItemLeaveType(ByVal id As String) As List(Of LeaveType)
        Dim jsonResponse As String = sqlManager.executeQuery("SELECT * FROM ref_leave_type WHERE ID = " + id)
        getItemLeaveType = JsonConvert.DeserializeObject(Of List(Of LeaveType))(jsonResponse)
    End Function
    Public Function insertLeaveType(ByVal leaveType As String, ByVal description As String) As List(Of LeaveType)
        sqlManager.executeUpdate("INSERT INTO ref_leave_type (type, description) VALUE ('" + leaveType + "','" + description + "')")
    End Function
    Public Function updateItemLeaveType(ByVal leaveType As String, ByVal description As String, ByVal id As String) As List(Of LeaveType)
        sqlManager.executeQuery("UPDATE ref_leave_type SET type = '" + leaveType + "',description = '" + description + "' WHERE id=" + id + "")
    End Function
    Public Function deleteItemLeaveType(ByVal id As String) As List(Of LeaveType)
        sqlManager.executeUpdate("DELETE FROM ref_leave_type WHERE id = " + id)
    End Function


    Public Function getAllTaxCode() As List(Of TaxCode)
        Dim taxCodeArr As List(Of TaxCode)
        Dim query As String = "SELECT * FROM ref_tax_code"
        Dim resultString As String = "" + sqlManager.executeQuery(query)
        taxCodeArr = JsonConvert.DeserializeObject(Of List(Of TaxCode))(resultString)
        Return taxCodeArr
    End Function

    Public Function getItemTaxCodeByID(ByVal id As String) As List(Of TaxCode)
        Dim taxCodeArr As New List(Of TaxCode)
        Dim taxCode As New TaxCode
        Dim query As String = "SELECT * FROM ref_tax_code WHERE id = '" + id + "'"
        Dim resultString As String = "" + sqlManager.executeQuery(query)

        taxCodeArr = JsonConvert.DeserializeObject(Of List(Of TaxCode))(resultString)
        For Each tempEmp As TaxCode In taxCodeArr
            taxCode = tempEmp
            Exit For
        Next
        Return taxCodeArr

    End Function

    Public Function getItemTaxCodeByTitle(ByVal title As String) As TaxCode
        Dim taxCodeArr As New List(Of TaxCode)
        Dim taxCode As New TaxCode
        Dim query As String = "SELECT * FROM ref_tax_code WHERE tax_code = '" + title + "'"
        Dim resultString As String = "" + sqlManager.executeQuery(query)

        taxCodeArr = JsonConvert.DeserializeObject(Of List(Of TaxCode))(resultString)
        For Each tempEmp As TaxCode In taxCodeArr
            taxCode = tempEmp
            Exit For
        Next
        Return taxCode

    End Function
    Public Function insertTaxCode(ByVal taxCode As String, ByVal description As String, ByVal excemption As String) As List(Of TaxCode)
        sqlManager.executeUpdate("INSERT INTO ref_tax_code (tax_code, tax_code_desc, exemption) VALUE ('" + taxCode + "','" + description + "'," + excemption + ")")
    End Function
    Public Function updateItemTaxCode(ByVal taxCode As String, ByVal description As String, ByVal excemption As String, ByVal id As String) As List(Of TaxCode)
        sqlManager.executeQuery("UPDATE ref_tax_code SET tax_code = '" + taxCode + "', tax_code_desc='" + description + "', exemption=" + excemption + " WHERE id=" + id + " ")
    End Function
    Public Function deleteItemTaxCode(ByVal id As String) As List(Of LeaveType)
        sqlManager.executeUpdate("DELETE FROM ref_tax_code WHERE id = " + id)
    End Function
    Public Function getAllTax() As List(Of Tax)
        Dim jsonResponse As String = sqlManager.executeQuery("SELECT * FROM tbl_tax")
        getAllTax = JsonConvert.DeserializeObject(Of List(Of Tax))(jsonResponse)
    End Function
    Public Function getItemTax(ByVal id As String) As List(Of Tax)
        Dim jsonResponse As String = sqlManager.executeQuery("SELECT * FROM tbl_tax WHERE ID = " + id)
        getItemTax = JsonConvert.DeserializeObject(Of List(Of Tax))(jsonResponse)
    End Function
    Public Function insertTax(ByVal taxCode As String, ByVal minimumSalary As String, ByVal maximumSalary As String, ByVal baseTax As String, ByVal percentageOver As String) As List(Of Tax)
        sqlManager.executeUpdate("INSERT INTO tbl_tax (tax_code_id,min_salary,max_salary,base_tax,percentage_over) VALUE (" + taxCode + "," + minimumSalary + "," + maximumSalary + "," + baseTax + "," + percentageOver + ")")
    End Function
    Public Function updateItemTax(ByVal taxCode As String, ByVal minimumSalary As String, ByVal maximumSalary As String, ByVal baseTax As String, ByVal percentageOver As String, ByVal id As String) As List(Of Tax)
        sqlManager.executeQuery("UPDATE tbl_tax SET tax_code_id=" + taxCode + " , min_salary=" + minimumSalary + ", max_salary=" + maximumSalary + ", base_tax=" + baseTax + ", percentage_over=" + percentageOver + " WHERE id=" + id + " ")
    End Function
    Public Function deleteItemTax(ByVal id As String) As List(Of Tax)
        sqlManager.executeUpdate("DELETE FROM tax_code_id WHERE id = " + id)
    End Function
    Public Function getAllHoliday() As List(Of Holiday)
        Dim jsonResponse As String = sqlManager.executeQuery("SELECT * FROM tbl_holiday")
        getAllHoliday = JsonConvert.DeserializeObject(Of List(Of Holiday))(jsonResponse)
    End Function
    Public Function getItemHoliday(ByVal id As String) As List(Of Holiday)
        Dim jsonResponse As String = sqlManager.executeQuery("SELECT * FROM tbl_holiday WHERE ID = " + id)
        getItemHoliday = JsonConvert.DeserializeObject(Of List(Of Holiday))(jsonResponse)
    End Function
    Public Function insertHoliday(ByVal holidayObject As Holiday)
        sqlManager.executeUpdate("INSERT INTO tbl_holiday (description,holiday_date,type) VALUES ('" + holidayObject.description.ToString() + "','" + holidayObject.holiday_date.ToString("yyyy-M-d") + "','" + holidayObject.type.ToString() + "')")
    End Function
    Public Function updateItemHoliday(ByVal holidayObject As Holiday)
        sqlManager.executeQuery("UPDATE tbl_holiday SET description='" + holidayObject.description.ToString() + "' , holiday_date='" + holidayObject.holiday_date.ToString("yyyy-M-d") + "', type='" + holidayObject.type.ToString() + "' WHERE id=" + holidayObject.id.ToString() + " ")
    End Function
    Public Function deleteItemHoliday(ByVal id As String) As List(Of Holiday)
        sqlManager.executeUpdate("DELETE FROM tbl_holiday WHERE id = " + id)
    End Function
    Public Function getSickLeaveByEmpId(ByVal empid As String) As List(Of Leave)
        Dim taxCodeArr As New List(Of Leave)
        Dim taxCode As New Leave
        Dim query As String = "SELECT * FROM tbl_leave WHERE employee_id = '" + empid + "' AND leave_type_id='1'"
        Dim resultString As String = "" + sqlManager.executeQuery(query)

        taxCodeArr = JsonConvert.DeserializeObject(Of List(Of Leave))(resultString)
        For Each tempEmp As Leave In taxCodeArr
            taxCode = tempEmp
            Exit For
        Next
        Return taxCodeArr

    End Function
    Public Function getVacationLeaveByEmpId(ByVal empid As String) As List(Of Leave)
        Dim taxCodeArr As New List(Of Leave)
        Dim taxCode As New Leave
        Dim query As String = "SELECT * FROM tbl_leave WHERE employee_id = '" + empid + "' AND leave_type_id='0'"
        Dim resultString As String = "" + sqlManager.executeQuery(query)

        taxCodeArr = JsonConvert.DeserializeObject(Of List(Of Leave))(resultString)
        For Each tempEmp As Leave In taxCodeArr
            taxCode = tempEmp
            Exit For
        Next
        Return taxCodeArr

    End Function
    Public Function getAllLeaveByEmpId(ByVal empid As String) As List(Of Leave)
        Dim taxCodeArr As New List(Of Leave)
        Dim taxCode As New Leave
        Dim query As String = "SELECT * FROM tbl_leave WHERE employee_id = '" + empid + "'"
        Dim resultString As String = "" + sqlManager.executeQuery(query)

        taxCodeArr = JsonConvert.DeserializeObject(Of List(Of Leave))(resultString)
        For Each tempEmp As Leave In taxCodeArr
            taxCode = tempEmp
            Exit For
        Next
        Return taxCodeArr

    End Function

    Public Function getAllLeave() As List(Of Leave)
        Dim jsonResponse As String = sqlManager.executeQuery("SELECT * FROM tbl_leave")
        getAllLeave = JsonConvert.DeserializeObject(Of List(Of Leave))(jsonResponse)
    End Function
    Public Function getItemLeave(ByVal id As String) As List(Of Leave)
        Dim jsonResponse As String = sqlManager.executeQuery("SELECT * FROM tbl_leave WHERE ID = " + id)
        getItemLeave = JsonConvert.DeserializeObject(Of List(Of Leave))(jsonResponse)
    End Function
    Public Function insertLeave(ByVal LeaveObject As Leave)
        sqlManager.executeUpdate("INSERT INTO tbl_leave (employee_id,leave_type_id,leave_from,leave_to,reason,status) VALUES ('" + LeaveObject.employee_id.ToString() + "','" + LeaveObject.leave_type_id.ToString() + "','" + LeaveObject.leave_from.ToString("yyyy-M-d") + "','" + LeaveObject.leave_to.ToString("yyyy-M-d") + "','" + LeaveObject.reason.ToString() + "','" + LeaveObject.status.ToString() + "')")
    End Function
    Public Function updateItemLeave(ByVal LeaveObject As Leave)
        sqlManager.executeQuery("UPDATE tbl_leave SET employee_id='" + LeaveObject.employee_id.ToString() + "' , leave_type_id='" + LeaveObject.leave_type_id.ToString() + "', leave_from='" + LeaveObject.leave_from.ToString("yyyy-M-d") + "', leave_to='" + LeaveObject.leave_to.ToString("yyyy-M-d") + "', reason='" + LeaveObject.reason.ToString() + "', status='" + LeaveObject.status.ToString() + "' WHERE id=" + LeaveObject.id.ToString() + " ")
    End Function
    Public Function deleteItemLeave(ByVal id As String) As List(Of Leave)
        sqlManager.executeUpdate("DELETE FROM tbl_leave WHERE id = " + id)
    End Function
    Public Function insertPayroll(ByVal PayrollObject As Payroll)
        Dim queryString As String = "INSERT INTO tbl_payroll (employee_id, date_from, date_to, salary, worked_hours, basic_pay, ot_hours, ot_pay, holiday_hours, holiday_pay, incentive_1_name, incentive_1_amount, incentive_2_name, incentive_2_amount, incentive_3_name, incentive_3_amount, sss_amount, pagibig_amount, philhealth_amount, deduction_1_name, deduction_1_amount, deduction_2_name, deduction_2_amount, deduction_3_name, deduction_3_amount, taxable_income, net_pay) " +
            "VALUES ('" + PayrollObject.employee_id.ToString() +
            "', '" + PayrollObject.date_from.ToString() +
            "', '" + PayrollObject.date_to.ToString() +
            "', '" + PayrollObject.salary.ToString() +
            "', '" + PayrollObject.worked_hours.ToString() +
            "', '" + PayrollObject.basic_pay.ToString() +
            "', '" + PayrollObject.ot_hours.ToString() +
            "', '" + PayrollObject.ot_pay.ToString() +
            "', '" + PayrollObject.holiday_hours.ToString() +
            "', '" + PayrollObject.holiday_pay.ToString() +
            "', '" + PayrollObject.incentive_1_name.ToString() +
            "', '" + If((PayrollObject.incentive_1_amount.ToString().Length <= 0), "0", PayrollObject.incentive_1_amount.ToString()) +
            "', '" + PayrollObject.incentive_2_name.ToString() +
            "', '" + If((PayrollObject.incentive_2_amount.ToString().Length <= 0), "0", PayrollObject.incentive_2_amount.ToString()) +
            "', '" + PayrollObject.incentive_3_name.ToString() +
            "', '" + If((PayrollObject.incentive_3_amount.ToString().Length <= 0), "0", PayrollObject.incentive_3_amount.ToString()) +
            "', '" + PayrollObject.sss_amount.ToString() +
            "', '" + PayrollObject.pagibig_amount.ToString() +
            "', '" + PayrollObject.philhealth_amount.ToString() +
            "', '" + PayrollObject.deduction_1_name.ToString() +
            "', '" + PayrollObject.deduction_1_amount.ToString() +
            "', '" + PayrollObject.deduction_2_name.ToString() +
            "', '" + PayrollObject.deduction_2_amount.ToString() +
            "', '" + PayrollObject.deduction_3_name.ToString() +
            "', '" + PayrollObject.deduction_3_amount.ToString() +
            "', '" + PayrollObject.taxable_income.ToString() +
            "', '" + PayrollObject.net_pay.ToString() + "')"
        sqlManager.executeUpdate(queryString)
    End Function
    Public Function updateItemPayroll(ByVal PayrollObject As Payroll)
        sqlManager.executeUpdate("UPDATE tbl_payroll SET employee_id='" + PayrollObject.employee_id.ToString() + "', date_from='" + PayrollObject.date_from.ToString() + "', date_to='" + PayrollObject.date_to.ToString() + "', salary='" + PayrollObject.salary.ToString() + "', worked_hours='" + PayrollObject.worked_hours.ToString() + "', basic_pay='" + PayrollObject.basic_pay.ToString() + "', ot_hours='" + PayrollObject.ot_hours.ToString() + "', ot_pay='" + PayrollObject.ot_pay.ToString() + "', holiday_hours='" + PayrollObject.holiday_hours.ToString() + "', holiday_pay='" + PayrollObject.holiday_pay.ToString() + "', incentive_1_name='" + PayrollObject.incentive_1_name.ToString() + "', incentive_1_amount='" + PayrollObject.incentive_1_amount.ToString() + "', incentive_2_name='" + PayrollObject.incentive_2_name.ToString() + "', incentive_2_amount='" + PayrollObject.incentive_2_amount.ToString() + "', incentive_3_name='" + PayrollObject.incentive_3_name.ToString() + "', incentive_3_amount='" + PayrollObject.incentive_3_amount.ToString() + "', sss_amount='" + PayrollObject.sss_amount.ToString() + "', pagibig_amount='" + PayrollObject.pagibig_amount.ToString() + "', philhealth_amount='" + PayrollObject.philhealth_amount.ToString() + "', deduction_1_name='" + PayrollObject.deduction_1_name.ToString() + "', deduction_1_amount='" + PayrollObject.deduction_1_amount.ToString() + "', deduction_2_name='" + PayrollObject.deduction_2_name.ToString() + "', deduction_2_amount='" + PayrollObject.deduction_2_amount.ToString() + "', deduction_3_name='" + PayrollObject.deduction_2_name.ToString() + "', deduction_3_amount='" + PayrollObject.deduction_3_amount.ToString() + "', taxable_income='" + PayrollObject.taxable_income.ToString() + "', net_pay='" + PayrollObject.net_pay.ToString() + "'WHERE id=" + PayrollObject.id.ToString() + " ")
    End Function
    Public Function getAllPayroll() As List(Of Payroll)
        Dim payrollArr As List(Of Payroll)
        Dim query As String = "SELECT * FROM tbl_payroll"
        Dim resultString As String = "" + sqlManager.executeQuery(query)
        payrollArr = JsonConvert.DeserializeObject(Of List(Of Payroll))(resultString)
        Return payrollArr
    End Function
    Public Function getPayrollId(ByVal empid As String) As List(Of Payroll)
        Dim PayrollArr As New List(Of Payroll)
        Dim Payroll As Payroll
        Dim query As String = "SELECT * FROM tbl_payroll WHERE employee_id = '" + empid + "'"
        Dim resultString As String = "" + sqlManager.executeQuery(query)

        PayrollArr = JsonConvert.DeserializeObject(Of List(Of Payroll))(resultString)
        For Each tempEmp As Payroll In PayrollArr
            Payroll = tempEmp
            Exit For
        Next
        Return PayrollArr

    End Function
    Public Function getPayrollByDate(ByVal empid As Integer, ByVal fromDate As String, ByVal toDate As String) As List(Of Payroll)
        Dim PayrollArr As New List(Of Payroll)
        Dim Payroll As Payroll
        Dim query As String = "SELECT * FROM tbl_payroll WHERE employee_id = '" + empid.ToString + "' and (date_from>= '" + fromDate + "' and date_to <= '" + toDate + "') ORDER BY date_from"
        Dim resultString As String = "" + sqlManager.executeQuery(query)

        PayrollArr = JsonConvert.DeserializeObject(Of List(Of Payroll))(resultString)
        For Each tempEmp As Payroll In PayrollArr
            Payroll = tempEmp
            Exit For
        Next
        Return PayrollArr

    End Function

    Public Function retrievePayrollByEmployeeIDArray(ByVal empIdList As List(Of String)) As List(Of Payroll)
        Dim payrollArr As New List(Of Payroll)
        If empIdList.Count <= 0 Then Return payrollArr
        Dim query As String = "SELECT * FROM tbl_payroll WHERE employee_id IN (" + String.Join(",", empIdList) + ")"
        Dim resultString As String = "" + sqlManager.executeQuery(query)

        payrollArr = JsonConvert.DeserializeObject(Of List(Of Payroll))(resultString)

        If (resultString = "") Then
            MsgBox("NO DATA")
        End If
        Return payrollArr
    End Function
    Public Function getAllOvertime() As List(Of Overtime)
        Dim overtimeArr As List(Of Overtime)
        Dim query As String = "SELECT * FROM tbl_overtime"
        Dim resultString As String = "" + sqlManager.executeQuery(query)
        overtimeArr = JsonConvert.DeserializeObject(Of List(Of Overtime))(resultString)
        Return overtimeArr
    End Function
    Public Function getOvertimeEmpId(ByVal empid As String) As List(Of Overtime)
        Dim overtimeArr As New List(Of Overtime)
        Dim Overtime As New Overtime
        Dim query As String = "SELECT * FROM tbl_overtime WHERE employee_id = '" + empid + "'"
        Dim resultString As String = "" + sqlManager.executeQuery(query)

        overtimeArr = JsonConvert.DeserializeObject(Of List(Of Overtime))(resultString)
        For Each tempEmp As Overtime In overtimeArr
            Overtime = tempEmp
            Exit For
        Next
        Return overtimeArr
    End Function
    Public Function getOvertimeId(ByVal id As String) As List(Of Overtime)
        Dim overtimeArr As New List(Of Overtime)
        Dim Overtime As New Overtime
        Dim query As String = "SELECT * FROM tbl_overtime WHERE id = '" + id + "'"
        Dim resultString As String = "" + sqlManager.executeQuery(query)

        overtimeArr = JsonConvert.DeserializeObject(Of List(Of Overtime))(resultString)
        For Each tempEmp As Overtime In overtimeArr
            Overtime = tempEmp
            Exit For
        Next
        Return overtimeArr
    End Function
    Public Function insertOvertime(ByVal OvertimeObject As Overtime)
        sqlManager.executeUpdate("INSERT INTO tbl_overtime (`employee_id`, `ot_from`, `ot_to`, `ot_hours`, `status`) VALUES ('" + OvertimeObject.employee_id.ToString() + "','" + OvertimeObject.ot_from.ToString("") + "','" + OvertimeObject.ot_to.ToString("") + "','" + OvertimeObject.ot_hours.ToString() + "','" + OvertimeObject.status.ToString() + "')")
    End Function
    Public Function updateItemOvertime(ByVal OvertimeObject As Overtime)
        sqlManager.executeQuery("UPDATE tbl_overtime SET employee_id='" + OvertimeObject.employee_id.ToString() + "' , ot_from='" + OvertimeObject.ot_from.ToString("") + "', ot_to='" + OvertimeObject.ot_to.ToString("") + "', ot_hours='" + OvertimeObject.ot_hours.ToString() + "', status='" + OvertimeObject.status.ToString() + "' WHERE id=" + OvertimeObject.id.ToString() + " ")
    End Function
    Public Function deleteItemOvertime(ByVal id As String) As List(Of Overtime)
        sqlManager.executeUpdate("DELETE FROM tbl_overtime WHERE id = " + id)
    End Function
End Class
